




import delimited data/passbook_activity_merged.csv, delim(",") clear

gen date = date(strofreal(tdate,"%8.0f"),"YMD")
* Check Date
tempvar tempdate
gen `tempdate' = date(month + " " + strofreal(day,"%2.0f") + ", " + strofreal(year,"%4.0f"), "MDY")
list if date != `tempdate'
format date %td
format name note %20s
order id acctno city name date transtype amt

// Change the date of the "BE" transaction to the final date (12,31,1874)
qui sum date
assert r(max)<mdy(12,31,1874) | mi(r(date))
replace date = mdy(12,31,1874) if transtype=="BE"

// Generate sort order for transactions on the same date
label define transaction_type_order 1 "BF" 2 "BF-D" 3 "BF-W" 4 "I" 5 "S" 6 "D" 7 "W" 8 "O" 9 "BI" 10 "BI-D" 11 "BI-W" 12 "BE" , replace
encode transtype,  g(transaction_type_order) label(transaction_type_order) noextend
assert !mi(transtype)


// Mark accounts as valid if there are no records tagged as invalid or interest-to-date
sort id date transaction_type_order amt
bys id (date transaction_type_order amt): egen any_invalid_record = max(invalid_entry)
bys id (date transaction_type_order amt): egen any_interest_to_date = max(interest_to_date)

// Drop them for this excercise
drop if any_invalid_record
drop if any_interest_to_date



// Mark dividend numbers where missing
gen dividend_number_not_noted = transtype =="I" & mi(dividend_number)
local divno_filter transtype == "I" & mi(dividend_number)
* Dividends paid in 1868
replace dividend_number =  3 ///
	if inrange(mofd(date), ym(1867,11), ym(1868,2)) & `divno_filter' 
replace dividend_number =  4 ///
	if inrange(mofd(date), ym(1868,3), ym(1868,6))  & `divno_filter'
replace dividend_number =  5 ///
	if inrange(mofd(date), ym(1868,7), ym(1868,10)) & `divno_filter' 
replace dividend_number =  6 ///
	if inrange(mofd(date), ym(1868,11), ym(1869,2)) & `divno_filter' 
replace dividend_number =  7 ///
	if inrange(mofd(date), ym(1869,3), ym(1869,6))  & `divno_filter'
replace dividend_number =  8 ///
	if inrange(mofd(date), ym(1869,7), ym(1869,10)) & `divno_filter' 
replace dividend_number =  9 ///
	if inrange(mofd(date), ym(1869,11), ym(1870,2)) & `divno_filter' 
replace dividend_number = 10 ///
	if inrange(mofd(date), ym(1870,3), ym(1870,6))  & `divno_filter'
replace dividend_number = 11 ///
	if inrange(mofd(date), ym(1870,7), ym(1870,10)) & `divno_filter' 
replace dividend_number = 12 ///
	if inrange(mofd(date), ym(1870,11), ym(1871,2)) & `divno_filter' 
replace dividend_number = 13 ///
	if inrange(mofd(date), ym(1871,3), ym(1871,6))  & `divno_filter'
*** TRANSITION TO NEW RULES
replace dividend_number = 14 ///
	if inrange(mofd(date), ym(1871,7), ym(1871,12)) & `divno_filter'  
replace dividend_number = 15 ///
	if inrange(mofd(date), ym(1872,1), ym(1872,6))  & `divno_filter' 
replace dividend_number = 16 ///
	if inrange(mofd(date), ym(1872,7), ym(1872,12)) & `divno_filter'  
replace dividend_number = 17 ///
	if inrange(mofd(date), ym(1873,1), ym(1873,6))  & `divno_filter' 
replace dividend_number = 18 ///
	if inrange(mofd(date), ym(1873,7), ym(1873,12)) & `divno_filter'  
replace dividend_number = 19 ///
	if inrange(mofd(date), ym(1874,1), ym(1874,6)) & `divno_filter'  

// Replace dividend payment dates with accrual dates
gen interest_date_replaced = !mi(date) & !mi(dividend_number)
replace date = mdy(7,1,1868)  if dividend_number == 5
replace date = mdy(11,2,1868) if dividend_number == 6
replace date = mdy(3,1,1869)  if dividend_number == 7
replace date = mdy(7,1,1869)  if dividend_number == 8
replace date = mdy(11,1,1869) if dividend_number == 9
replace date = mdy(3,1,1870)  if dividend_number == 10
replace date = mdy(7,1,1870)  if dividend_number == 11
replace date = mdy(11,1,1870) if dividend_number == 12
replace date = mdy(3,1,1871)  if dividend_number == 13
replace date = mdy(7,3,1871)  if dividend_number == 14
replace date = mdy(1,1,1872)  if dividend_number == 15
replace date = mdy(7,1,1872)  if dividend_number == 16
replace date = mdy(1,1,1873)  if dividend_number == 17
replace date = mdy(7,1,1873)  if dividend_number == 18
replace date = mdy(1,1,1874)  if dividend_number == 19


la define dividend ///
    3  "Nov 67 (3)"    ///
    4  "March 69 (4)"  ///
    5  "July 69 (5)"   ///
    6  "Nov 69 (6)"    ///
    7  "March 70 (7)"  ///
    8  "July 70 (8)"   ///
    9  "Nov 70 (9)"    ///
    10 "March 71 (10)" ///
    11 "July 71 (11)"  ///
    12 "Nov 71 (12)"   ///
    13 "March 72 (13)" ///
    14 "July 72 (14)"  ///
    15 "Jan 72 (15)"   ///
    16 "July 72 (16)"  ///
    17 "Jan 73 (17)"   ///
    18 "July 73 (18)"  ///
    19 "Jan 74 (19)"   ///
    , replace

la val dividend_number dividend



bys id (date transaction_type_order amt): assert transtype !="BE" if _n!=_N 

gen balance_change = .
replace balance_change = -amt if inlist(transtype, "W", "BF-W")
replace balance_change = amt if inlist(transtype, "D", "I", "BF", "BF-D")
bys id (date transaction_type_order amt): gen running_total = sum(balance_change)
order id name date transtype amt dividend_number balance_change running_total

// Keep only records for which a final balance is recorder and it is within 5 cents of the running total
bys id (date transaction_type_order amt): keep if transtype[_N]=="BE"
bys id (date transaction_type_order amt): keep if abs(amt[_N]-running_total[_N])<.05

save working/passbook_regimes, replace

